//
//  VHLSQLiteDatabase.swift
//  VHLSqlite
//
//  Created by Vincent on 2021/6/8.
//

import Foundation
import SQLite3

let SQLITE_DATE = SQLITE_NULL + 1
private let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

public class VHLSQLiteDataBase: NSObject {
    // 数据库地址类型
    public enum Location: CustomStringConvertible {
        case inMemory
        case temporary
        case uri(String)
        
        public var description: String {
            switch self {
            case .inMemory:
                return ":memory:"
            case .temporary:
                return ""
            case .uri(let URI):
                return URI
            }
        }
    }
    
    public static var shared: VHLSQLiteDataBase = {
        return try! VHLSQLiteDataBase(direcotryBase: .documentDirectory, direcotry: "NetWanderer", fileName: "NetWanderer.sqlite3", readOnly: false, busyTimeout: 5)
    }()
    /// sqlite 数据库连接句柄
    var db: OpaquePointer? = nil
    /// 数据库连接地址
    private(set) var dbLocation: Location? = nil
    
    // 操作队列
    var queue: DispatchQueue = DispatchQueue(label: "com.thinkerride.sqlite", attributes: [])
    fileprivate static let queueKey = DispatchSpecificKey<Int>()
    fileprivate lazy var queueContext: Int = unsafeBitCast(self, to: Int.self)
    
    // MARK: 记录数据的 表名/表结构信息
    fileprivate var tableInfos: [String: [String]] = [:]
    
    deinit {
        // 断开连接
        if db != nil {
            sqlite3_close(db)
            db = nil
            sync{ tableInfos = [:] }
        }
    }
    private override init() {
        super.init()
    }
    
    public init(_ location: Location = .inMemory,
                readOnly: Bool = false,
                busyTimeout: Double = 5.0) throws {
        super.init()

        dbLocation = location
        let cPath = location.description.cString(using: .utf8)
        let flags = readOnly ? SQLITE_OPEN_READONLY : SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE
        // 构建数据库连接
        let resultCode = sqlite3_open_v2(cPath!, &db, flags | SQLITE_OPEN_FULLMUTEX, nil)
        if resultCode != SQLITE_OK {
            let errorMessge = String(cString: sqlite3_errmsg(db))
            sqlite3_close(db)
            VHLSQLitePrint("VHLSQLite 数据库连接错误", errorMessge)
            throw NSError(domain: "com.thinkerride.sqlite.error", code: Int(resultCode), userInfo: ["messge": errorMessge])
        } else {
            VHLSQLitePrint("VHLSQLite 数据库连接成功:", location.description)
        }
        /// 设置超时时间
        sqlite3_busy_timeout(db, Int32(busyTimeout * 1_000))
        
        // 标记当前线程队列
        queue.setSpecific(key: VHLSQLiteDataBase.queueKey, value: queueContext)
    }
    public convenience init(_ filename: String,
                            readOnly: Bool = false,
                            busyTimeout: Double = 5.0) throws {
        try self.init(.uri(filename), readOnly: readOnly)
    }
    public convenience init(direcotryBase: FileManager.SearchPathDirectory = .documentDirectory,
                            direcotry: String,
                            fileName: String,
                            readOnly: Bool = false,
                            busyTimeout: Double = 5.0) throws {
        /// 数据库路径
        guard let path = VHLSQLiteTool.dbPath(with: direcotryBase, direcotry: direcotry, fileName: fileName) else {
            throw NSError(domain: "com.thinkerride.sqlite", code: -10, userInfo: ["messge": "数据库地址错误"])
        }
        try self.init(path, readOnly: readOnly, busyTimeout: busyTimeout)
    }
    
    // MARK: 队列方法
    func sync<T>(_ block: () throws -> T) rethrows -> T {
        if DispatchQueue.getSpecific(key: VHLSQLiteDataBase.queueKey) == queueContext {
            return try block()
        } else {
            return try queue.sync(execute: block)
        }
    }
}

// MARK: - 预处理 SQL 语句
fileprivate extension VHLSQLiteDataBase {
    // MARK: 预处理 SQL 语句
    private func prepare(_ sql: String, params: [Any]?) throws -> OpaquePointer? {
        guard let db = db else { return nil }
        
        var stmt: OpaquePointer?
        let cSQL = sql.cString(using: .utf8)
        
        // Prepare
        let result = sqlite3_prepare_v2(db, cSQL, -1, &stmt, nil)
        if result != SQLITE_OK {
            sqlite3_finalize(stmt)
            if let error = String(validatingUTF8: sqlite3_errmsg(self.db)) {
                let msg = "VHLSQLite - 无法预处理 SQL. SQL: \(sql), Error: \(error)"
                VHLSQLitePrint(msg)
                throw NSError(domain: "com.thinkerride.sqlite", code: -101, userInfo: ["messge": msg])
            }
            return nil
        }
        // 绑定参数
        if let params = params {
            let cntParams = sqlite3_bind_parameter_count(stmt)
            let cnt = params.count
            // 如果 sql 中需要绑定的参数个数和数组中的个数不一致，那么直接返回错误
            if cntParams != CInt(cnt) {
                let msg = "VHLSQLite - 绑定参数个数不一致. SQL: \(sql), Parameters: \(params)"
                VHLSQLitePrint(msg)
                throw NSError(domain: "com.thinkerride.sqlite", code: -102, userInfo: ["messge": msg])
            }
            
            var flag: CInt = 0
            for ndx in 1...cnt {
                
                if let value = params[ndx - 1] as? Int {
                    if value >= 2147483647, let value = params[ndx - 1] as? Double {
                        flag = sqlite3_bind_double(stmt, CInt(ndx), value)
                    }else{
                        flag = sqlite3_bind_int(stmt, CInt(ndx), CInt(value))
                    }
                } else if let string = params[ndx - 1] as? String {
                    flag = sqlite3_bind_text(stmt, CInt(ndx), string, -1, SQLITE_TRANSIENT)
                } else if let value = params[ndx - 1] as? Bool {
                    let num = value ? 1 : 0
                    flag = sqlite3_bind_int(stmt, CInt(ndx), CInt(num))
                } else if let value = params[ndx - 1] as? Double {
                    flag = sqlite3_bind_double(stmt, CInt(ndx), value)
                } else if let data = params[ndx - 1] as? NSData {
                    flag = sqlite3_bind_blob(stmt, CInt(ndx), data.bytes, CInt(data.length), SQLITE_TRANSIENT)
                } else if let date = params[ndx - 1] as? Date {
                    let dateString = VHLSQLiteTool.dateFormatter.string(from: date)
                    flag = sqlite3_bind_text(stmt, CInt(ndx), dateString, -1, SQLITE_TRANSIENT)
                } else {
                    flag = sqlite3_bind_null(stmt, CInt(ndx))
                }
                
                if flag != SQLITE_OK {
                    sqlite3_finalize(stmt)
                    if let error = String(validatingUTF8: sqlite3_errmsg(self.db)) {
                        let msg = "VHLSQLite - 绑定错误 SQL: \(sql), Parameters: \(params), Index: \(ndx) Error: \(error)"
                        VHLSQLitePrint(msg)
                        throw NSError(domain: "com.thinkerride.sqlite", code: -103, userInfo: ["messge": msg])
                    }
                    return nil
                }
            }
        }
        
        return stmt
    }
    
    // MARK: 执行 STMT
    private func execute(stmt: OpaquePointer, sql: String) throws -> Int {
        guard db != nil else { return -1 }
        
        let res = sqlite3_step(stmt)        // 执行 SQL
        defer { sqlite3_finalize(stmt) }
        
        if res == SQLITE_ROW {
            VHLSQLitePrint("VHLSQLite - 这是一个查询，请使用 query 进行操作")
            return -1
        }
        if res != SQLITE_OK && res != SQLITE_DONE {
            sqlite3_finalize(stmt)
            if let error = String(validatingUTF8: sqlite3_errmsg(self.db)) {
                let msg = "VHLSQLite - failed to execute SQL: \(sql), Error: \(error)"
                VHLSQLitePrint(msg)
                throw NSError(domain: "com.thinkerride.sqlite", code: -201, userInfo: ["messge": error])
            }
            return -1
        }
        
        // Insert
        let upp = sql.uppercased()
        var result = 0
        if upp.hasPrefix("INSERT ") {
            let rid = sqlite3_last_insert_rowid(db)
            result = Int(rid)
        } else if upp.hasPrefix("DELETE") || upp.hasPrefix("UPDATE") {
            var cnt = sqlite3_changes(db)
            if cnt == 0 {
                cnt += 1
            }
            result = Int(cnt)
        } else {
            result = 1
        }
        
        return result
    }
    
    // MARK: 查询 STMT
    private func query(stmt: OpaquePointer, sql: String) -> [[String: Any]] {
        var rows: [[String: Any]] = []
        var fetchColumnInfo = true
        var columnCount: CInt = 0
        var columnNames: [String] = []
        var columnTypes: [CInt] = []
        
        var result = sqlite3_step(stmt)     // 执行 sql
        defer { sqlite3_finalize(stmt) }
        
        while result == SQLITE_ROW {
            // 获取表类型
            if fetchColumnInfo {
                columnCount = sqlite3_column_count(stmt)
                for index in 0..<columnCount {
                    if let name = sqlite3_column_name(stmt, index) {
                        columnNames.append(String(validatingUTF8: name)!)
                        columnTypes.append(getColumnType(index: index, stmt: stmt))
                    }
                    fetchColumnInfo = false
                }
            }
            // 获取内容
            var row: [String: Any] = [:]
            for index in 0..<columnCount {
                let key = columnNames[Int(index)]
                let type = columnTypes[Int(index)]
                if let val = getColumnValue(index: index, type: type, stmt: stmt) {
                    row[key] = val
                }
            }
            
            rows.append(row)
            result = sqlite3_step(stmt)
        }
        
        return rows
    }
}

// MARK: - GET
fileprivate extension VHLSQLiteDataBase {
    // MARK: 获取字段类型
    func getColumnType(index: CInt, stmt: OpaquePointer) -> CInt {
        // Column types - http://www.sqlite.org/datatype3.html (section 2.2 table column 1)
        let blobTypes = ["BINARY", "BLOB", "VARBINARY"]
        let charTypes = ["CHAR", "CHARACTER", "CLOB", "NATIONAL VARYING CHARACTER", "NATIVE CHARACTER", "NCHAR", "NVARCHAR", "TEXT", "VARCHAR", "VARIANT", "VARYING CHARACTER"]
        let dateTypes = ["DATE", "DATETIME", "TIME", "TIMESTAMP"]
        let intTypes = ["BIGINT", "BIT", "BOOL", "BOOLEAN", "INT", "INT2", "INT8", "INTEGER", "MEDIUMINT", "SMALLINT", "TINYINT"]
        let nullTypes = ["NULL"]
        let realTypes = ["DECIMAL", "DOUBLE", "DOUBLE PRECISION", "FLOAT", "NUMERIC", "REAL"]
        
        guard let buf = sqlite3_column_decltype(stmt, index) else {
            return sqlite3_column_type(stmt, index)
        }
        var tmp = String(validatingUTF8: buf)!.uppercased()
        if let pos = tmp.range(of: "(") {
            tmp = String(tmp[..<pos.lowerBound])
        }

        if intTypes.contains(tmp) { return SQLITE_INTEGER }
        if realTypes.contains(tmp) { return SQLITE_FLOAT }
        if charTypes.contains(tmp) { return SQLITE_TEXT }
        if blobTypes.contains(tmp) { return SQLITE_BLOB }
        if nullTypes.contains(tmp) { return SQLITE_NULL }
        if dateTypes.contains(tmp) { return SQLITE_DATE }
        
        return SQLITE_TEXT
    }
    
    // MARK: 获取字段的值
    func getColumnValue(index: CInt, type: CInt, stmt: OpaquePointer) -> Any? {
        // Integer
        if type == SQLITE_INTEGER {
            return Int(sqlite3_column_int64(stmt, index))
        }
        // Float
        if type == SQLITE_FLOAT {
            return Double(sqlite3_column_double(stmt, index))
        }
        // Blob
        if type == SQLITE_BLOB {
            let data = sqlite3_column_blob(stmt, index)
            let size = sqlite3_column_bytes(stmt, index)
            let value = NSData(bytes: data, length: Int(size))
            return value
        }
        // Date
        if type == SQLITE_DATE {
            // 文字类型的时间
            if let ptr = UnsafeRawPointer(sqlite3_column_text(stmt, index)) {
                let uptr = ptr.bindMemory(to: CChar.self, capacity: 0)
                if var txt = String(validatingUTF8: uptr) {
                    if txt.count == 10 {
                        txt += " 00:00:00"
                    }
                    if let date = VHLSQLiteTool.dateFormatter.date(from: txt) {
                        return date
                    } else {
                        NSLog("VHLSQLite : String value: \(txt) but could not be converted to date!")
                    }
                }
            }
            // 如果是时间戳类型的时间
            let val = sqlite3_column_double(stmt, index)
            let db = Date(timeIntervalSince1970: val)
            return db
        }
        // Ohter
        if let ptr = UnsafeRawPointer(sqlite3_column_text(stmt, index)) {
            let uptr = ptr.bindMemory(to: CChar.self, capacity: 0)
            let txt = String(validatingUTF8: uptr)
            return txt
        }
        // Null
        if type == SQLITE_NULL {
            return nil
        }
        return nil
    }
}

// MARK: - 公开方法 执行 SQL
public extension VHLSQLiteDataBase {
    // MARK: 执行
    func execute(_ sql: String, params: [Any]? = nil) throws -> Int {
        var result = 0
        try sync {
            if let stmt = try self.prepare(sql, params: params) {
                result = try execute(stmt: stmt, sql: sql)
            }
        }
        
        return result
    }
    
    // MARK: 查询
    func query(_ sql: String, params: [Any]? = nil) throws -> [[String: Any]] {
        var rows: [[String: Any]] = []
        try sync {
            if let stmt = try prepare(sql, params: params) {
                rows = self.query(stmt: stmt, sql: sql)
            }
        }
        return rows
    }
    
    // MARK: 查询返回第一行第一个的值
    func scalar(_ sql: String, params: [Any]? = nil) throws -> Any? {
        let result = try query(sql, params: params)
        if result.count == 0 { return nil }
        
        return result[0].first?.value
    }
    
    // MARK: 事务执行
    func transaction(_ block: () throws -> Void) throws {
        try sync {
            sqlite3_exec(db, "BEGIN TRANSACTION;", nil, nil, nil)
            do {
                try block()
                sqlite3_exec(db, "COMMIT TRANSACTION;", nil, nil, nil)
            } catch {
                sqlite3_exec(db, "ROLLBACK TRANSACTION;", nil, nil, nil)
                throw error
            }
        }
    }
}

// MARK: - 数据库版本
public extension VHLSQLiteDataBase {
    // MARK: 获取数据库版本
    func getVersion() -> Int {
        var version = 0
        if let column = try? query("PRAGMA user_version").first {
            version = column["user_version"] as? Int ?? 0
        }
        return version
    }
    
    // MARK: 设置数据库版本
    @discardableResult
    func set(dbVersion: Int) -> Int {
        let sql = "PRAGMA user_version=\(dbVersion)"
        let result = try? execute(sql)
        return result ?? 0
    }
}

// ---------------------------------------------------------------------------------------
// MARK: - 数据库 / 表操作
public extension VHLSQLiteDataBase {
    /// 数据库 表是否存在
    func tableExists(tableName: String) -> Bool {
        let isExist = sync{ tableInfos.keys.contains(tableName) }
        if isExist { return true }
        
        let sql = "SELECT EXISTS (SELECT * FROM sqlite_master WHERE type = 'table' AND name = ?) AS 'EXISTS'"
        // 查询返回结果为
        if let isExist = try? scalar(sql, params: [tableName]) as? Int, isExist == 1 {
            sync { tableInfos[tableName] = [] }
            return true
        }
        
        return false
    }
    
    /// 单个对象是否存在
    func objectExists(_ object: VHLSQLiteObject) -> Bool {
        let sql = object.selectSelfSQL()
        guard let results = try? query(sql) else { return false }
        return results.count > 0
    }
}

public extension VHLSQLiteDataBase {
    /// 创建表
    @discardableResult
    func createTable(object: VHLSQLiteObject) throws -> Bool {
        let tableName = object.tableName
        if tableName == "" { return false }
        
        // 表是否存在
        var createResult = true
        if !tableExists(tableName: tableName) {
            let createTableSQL = object.createTableSQL()
            createResult = try execute(createTableSQL) > 0
            
            if createResult { sync{ tableInfos[tableName] = [] } }
        }
        
        // 更新表字段
        for property in object.getProperties() {
            try addColumnIfNoExist(object, column: property)
        }

        return createResult
    }
    
    /// 清空表
    @discardableResult
    func cleanTable(_ tableName: String) throws -> Bool {
        let cleanSQL = "DELETE FROM \(tableName)"
        let result = try execute(cleanSQL) > 0
        return result
    }
    
    /// 删除表
    @discardableResult
    func dropTable(_ tableName: String) throws -> Bool {
        let dropSQL = "DROP TABLE IF EXISTS \(tableName)"
        let result = try execute(dropSQL)
        if result > 0 { sync{ tableInfos[tableName] = nil } }
        return result > 0
    }
}

// MARK: - 字段操作
public extension VHLSQLiteDataBase {
    //MARK: 添加不存在的字段
    @discardableResult
    func addColumnIfNoExist(_ object: VHLSQLiteObject, column: VHLSQLiteProperty) throws -> Bool {
        // 字段已存在
        let tableName = object.tableName
        let columns = sync{ tableInfos[tableName] } ?? []
        
        if let _ = columns.firstIndex(where: { (existColumn) -> Bool in
            return existColumn == column.key
        }) {
            return false
        }

        // 查询表信息
        let tableInfoSQL = object.tableInfoSQL()
        let existsColumnNames = try query(tableInfoSQL).map({ (dic:[String : Any]) -> String in
            (dic["name"] as? String) ?? ""
        })
        sync{ tableInfos[tableName] = existsColumnNames }
        
        // 判断是否存在，不存在则添加
        if existsColumnNames.firstIndex(where: { (existColumn) -> Bool in
            return existColumn == column.key
        }) == nil {
            let addColumnSQL = object.addCloumnSQL(column.key, column.sqlType.rawValue)
            return try execute(addColumnSQL) > 0
        }
        
        return false
    }
}

// MARK: - 增删改查
public extension VHLSQLiteDataBase {
    // MARK: 保存
    func save(_ object: VHLSQLiteObject) throws -> Bool {
        // 建表
        try createTable(object: object)
        let (sql, params) = object.saveOrUpdateSQLParams(isInsert: true)
        return try execute(sql, params: params) > 0
    }
    // MARK: 保存修改
    func saveOrUpdate(_ object: VHLSQLiteObject) throws -> Bool {
        // 建表
        try createTable(object: object)
        
        let isExists = objectExists(object)
        let (sql, params) = object.saveOrUpdateSQLParams(isInsert: !isExists)
        
        return try execute(sql, params: params) > 0
    }
    
    // MARK: 删除
    @discardableResult
    func delete(_ object: VHLSQLiteObject) throws -> Bool {
        let tableName = object.tableName
        var sql = "DELETE FROM \(tableName)"

        let whereSQL = object.whereSelfSQL()
        if !whereSQL.isEmpty {
            sql += " WHERE \(whereSQL)"
        }
        
        return try execute(sql) > 0
    }
}

/**
 Tips：
 多线程多字典进行读写时，当多线程同时操作同一个 key 会发生野指针闪退  EXC_BAD_ACCESS
 修复方法为：
 1. 加锁。性能不高
 2. GCD 线程管理
 
 测试例子
 var tableInfos: [String: [String]] = [:]
 let queue = DispatchQueue(label: "cn.vincents.queue")
 for i in 0..<1000 {
     DispatchQueue.global().async {
         let a = ["\(i)"]
         queue.async {
             DPrint(tableInfos.keys.contains("test"))
         }
         queue.async {
             tableInfos["test"] = a
         }
     }
 }
 */
